﻿/*
    2014-06-27  博客园共享 http://www.cnblogs.com/xtdhb/
    作者:张存
    QQ:  55500515
*/

using System;
using System.Collections.Generic;
using System.Text;
using ZhCun.Framework.Common.Models.TableModel;
using ZhCun.Framework.DataAccess.DbHelper;
using ZhCun.Framework.DataAccess.BuildSQLText;
using System.Data.Common;
using ZhCun.Framework.Common.Models;
using ZhCun.Framework.Common.Exceptions;
using ZhCun.Framework.Common.Models.ProcedureModel;
using System.Data;
using ZhCun.Framework.Common.Models.WhereHelperModel;

namespace ZhCun.Framework.DataAccess.DAL
{
    public abstract class DalBase
    {

        #region 抽象方法,由具体数据类实现

        /// <summary>
        /// 访问数据的DBHelper对象
        /// </summary>
        public abstract DbHelperBase DBHelper { get; } //子类实现
        /// <summary>
        /// 获得生成sql文本的对象
        /// </summary>
        protected internal abstract BuildSQL BuildSQLTextObj { get; }
        /// <summary>
        /// 获得数据参数对象
        /// </summary>
        protected internal abstract DbParameter GetDbParam(string paramName, object paramValue);
        /// <summary>
        /// 创建WhereHelper对象
        /// </summary>
        internal abstract WhereHelper CreateWhereHelper();        

        #endregion

        #region 生成sql语句的方法

        /// <summary>
        /// 生成Insert SQL语句
        /// </summary>
        protected internal virtual void GenerateInsertSQLTextAndParam<TModel>(out string sqlText, out Dictionary<string, object> dbParams, params TModel[] models) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateInsertSQLTextAndParam(out sqlText, out dbParams, models);
        }
        /// <summary>
        /// 生成Update SQL语句
        /// </summary>
        protected internal virtual void GenerateUpdateSQLTextAndParam<TModel>(out string sqlText, ref Dictionary<string, object> dbParams, string sqlWhere, string[] updateFields, params TModel[] models) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateUpdateSQLTextAndParam(out sqlText, ref dbParams, sqlWhere, updateFields, models);
        }
        /// <summary>
        /// 生成Delete SQL语句
        /// </summary>
        protected internal virtual void GenerateDeleteSQLTextAndParam<TModel>(out string sqlText, out Dictionary<string, object> dbParams, params object[] pkValues) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateDeleteSQLTextAndParam<TModel>(out sqlText, out dbParams, pkValues);
        }
        /// <summary>
        /// 生成判断存在的 SQL语句
        /// </summary>
        protected internal virtual void GenerateIsExistSQLText<TModel>(object pkeyValue, string fieldName, object fieldValue, string sqlWhere, ref Dictionary<string, object> dbParams, out string sqlText) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateIsExistSQLText<TModel>(pkeyValue, fieldName, fieldValue, sqlWhere, ref dbParams, out sqlText);
        }
        /// <summary>
        /// 生成统计数量的 SQL语句
        /// </summary>
        protected internal virtual void GenerateCountSQLTextAndParam<TModel>(string sqlWhere, Dictionary<string, object> dbParams, out string sqlText) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateCountSQLTextAndParam<TModel>(sqlWhere, dbParams, out sqlText);
        }
        /// <summary>
        /// 生成普通查询的SQL语句
        /// </summary>
        protected internal virtual void GenerateSearchSQLTextAndParam<TModel>(string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, out string sqlText, params string[] selectFields) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateSearchSQLTextAndParam<TModel>(sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
        }
        /// <summary>
        /// 生成前几行数据的SQL语句
        /// </summary>
        protected internal virtual void GenerateTopCountSQLText<TModel>(int topCount, string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, out string sqlText, params string[] selectFields) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GenerateTopCountSQLText<TModel>(topCount, sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
        }
        /// <summary>
        /// 生成分页的sql语句SQL语句
        /// </summary>
        protected internal virtual void GeneratePageSQLTextAndParam<TModel>(int onePageCount, int pageNo, bool isDesc, string[] orderFields, int recordCount, string sqlWhere, Dictionary<string, object> dbParams, out string sqlText, params string[] selectFields) where TModel : ModelBase, new()
        {
            BuildSQLTextObj.GeneratePageSQLTextAndParam<TModel>(onePageCount, pageNo, isDesc, orderFields, recordCount, sqlWhere, dbParams, out sqlText, selectFields);
        }

        #endregion

        #region 生成数据库参数对象

        /// <summary>
        /// 返回SQL参数查询.
        /// Key如果不包含参数的@符号,则加上
        /// </summary>
        protected virtual DbParameter[] GetDbParam(Dictionary<string, object> dbParamsDict)
        {
            if (dbParamsDict == null || dbParamsDict.Keys.Count == 0) return null;
            List<DbParameter> paramList = new List<DbParameter>();
            foreach (var item in dbParamsDict.Keys)
            {
                string pName = item;
                if (item.Trim().Substring(0, 1) != "@")
                {
                    pName = "@" + pName;
                }
                paramList.Add(GetDbParam(item, dbParamsDict[item]));
            }
            return paramList.ToArray();
        }

        #endregion

        #region 获取数据库服务器的时间

        public abstract DateTime GetNowTime();

        #endregion

        #region 事务操作

        public void TransStart()
        {
            DBHelper.TransStart();
        }
        public void TransCommit()
        {
            DBHelper.TransCommit();
        }
        public void TransRollback()
        {
            DBHelper.TransRollback();
        }

        #endregion

        #region 执行的sql语句,增删改

        /// <summary>
        /// 抛出Model为空的异常
        /// </summary>
        protected void ThrowModelIsNullException<TModel>(params TModel[] models) where TModel : ModelBase, new()
        {
            if (models == null || models.Length == 0)
            {
                string errMsg = string.Format("在 {0} 对象中执行增加时失败,实体model为空!", BuildSQLTextObj.GetTableName<TModel>());
                throw new FrameworkException(errMsg);
            }
        }
        /// <summary>
        /// 执行sql语句返回所影响行数
        /// </summary>
        public virtual int ExecuteBySQL(string sqlText, Dictionary<string, object> dbParams)
        {
            //执行sql语句的操作都由此方法实现
            DbParameter[] parameters = GetDbParam(dbParams);
            int rows = DBHelper.ExecNonQuery(sqlText, parameters);
            return rows;
        }
        /// <summary>
        /// 新增1条或多条
        /// </summary>
        public virtual int Add<TModel>(params TModel[] models) where TModel : ModelBase, new()
        {
            ThrowModelIsNullException(models);
            string sqlText;
            Dictionary<string, object> dbParams;
            GenerateInsertSQLTextAndParam(out sqlText, out dbParams, models);
            int rows = ExecuteBySQL(sqlText, dbParams);
            return rows;
        }
        /// <summary>
        /// 更新一条或多条记录,根据sql条件,指定更新字段(sqlWhere为空,则按主键更新)
        /// </summary>
        public virtual int Update<TModel>(string[] fields, string sqlWhere, Dictionary<string, object> dbParams, params TModel[] models) where TModel : ModelBase, new()
        {
            ThrowModelIsNullException(models);
            string sqlText;
            GenerateUpdateSQLTextAndParam(out sqlText, ref dbParams, sqlWhere, fields, models);
            int rows = ExecuteBySQL(sqlText, dbParams);
            return rows;
        }
        /// <summary>
        /// 更新一条或多条记录,根据sql条件,指定忽略更新的字段
        /// </summary>
        public virtual int UpdateByIgnoreField<TModel>(string[] ignoreFields, string sqlWhere, Dictionary<string, object> dbParams, params TModel[] models) where TModel : ModelBase, new()
        {
            string[] allFields = BuildSQLTextObj.GetAllFields<TModel>();
            string[] updateFields = BuildSQLTextObj.RemoveFields(allFields, ignoreFields);
            return Update(updateFields, sqlWhere, dbParams, models);
        }
        /// <summary>
        /// 根据主键删除记录
        /// </summary>
        public virtual int Delete<TModel>(params object[] pkValues) where TModel : ModelBase, new()
        {
            string sqlText;
            Dictionary<string, object> dbParams;
            if (pkValues == null || pkValues.Length < 0)
            {
                throw new DbDataException("删除操作时主键为空!");
            }
            GenerateDeleteSQLTextAndParam<TModel>(out sqlText, out dbParams, pkValues);
            int rows = ExecuteBySQL(sqlText, dbParams);
            return rows;
        }

        #endregion

        #region 判断数据值是否存在

        public virtual bool IsExist<TModel>(object pkeyValue, string fieldName, object fieldValue, string sqlWhere, Dictionary<string, object> dbParams) where TModel : ModelBase, new()
        {
            string sqlText;
            GenerateIsExistSQLText<TModel>(pkeyValue, fieldName, fieldValue, sqlWhere, ref dbParams, out sqlText);
            DbParameter[] param = GetDbParam(dbParams);
            object obj = DBHelper.GetScalar(sqlText, param);
            int r = Convert.ToInt32(obj);
            return r > 0 ? true : false;
        }

        #endregion

        #region 根据主键获得首行首列

        /// <summary>
        /// 根据主键获得一个指定字段的值
        /// </summary>
        public virtual object GetObjectByField<T>(string fieldName, object pkValue) where T : ModelBase, new()
        {
            StringBuilder strSql = new StringBuilder();
            string tableName = BuildSQLTextObj.GetTableName<T>();
            string pkName = BuildSQLTextObj.GetPrimaryKeyName<T>();
            strSql.AppendFormat("Select {0} From {1}", fieldName, tableName);
            strSql.AppendFormat(" Where {0}=@{0} ", pkName);
            Dictionary<string, object> dbParams = new Dictionary<string, object>();
            dbParams.Add(pkName, pkValue);
            DbParameter[] param = GetDbParam(dbParams);
            object obj = DBHelper.GetScalar(strSql.ToString(), param);
            return obj;
        }

        #endregion

        #region 根据主键获得一个实体对象

        public virtual TModel GetModel<TModel>(object id) where TModel : ModelBase, new()
        {
            StringBuilder sp = new StringBuilder();
            string pkName = BuildSQLTextObj.GetPrimaryKeyName<TModel>();
            sp.AppendFormat("where {0}=@{0}", pkName);
            Dictionary<string, object> dbParams = new Dictionary<string, object>();
            dbParams.Add("@" + pkName, id);
            List<TModel> list = GetList<TModel>(sp.ToString(), dbParams, null, false, null);
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        #endregion

        #region 获得记录数

        //获得记录数,如果返回非数字后
        public virtual int GetRecordCount<TModel>(string sqlWhere, Dictionary<string, object> dbParams) where TModel : ModelBase, new()
        {
            string sqlText;
            GenerateCountSQLTextAndParam<TModel>(sqlWhere, dbParams, out sqlText);
            DbParameter[] param = GetDbParam(dbParams);
            object obj = DBHelper.GetScalar(sqlText, param);
            if (obj == null) return 0;
            int recCount = Convert.ToInt32(obj);
            return recCount;
        }

        #endregion

        #region 获得DataTable的几个方法

        public virtual DataTable GetDataTableBySQL(string sqlText, Dictionary<string, object> dbParams)
        {
            DbParameter[] param = GetDbParam(dbParams);
            DataTable dt = DBHelper.GetDataTable(sqlText, param);
            return dt;
        }
        public virtual DataTable GetDataTable<TModel>(string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, params string[] selectFields) where TModel : ModelBase, new()
        {
            string sqlText;
            GenerateSearchSQLTextAndParam<TModel>(sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
            return GetDataTableBySQL(sqlText, dbParams);
        }
        public virtual DataTable GetDataTableByPage<TModel>(int onePageCount, int pageNo, bool isDesc, string[] orderField, out int pageCount, out int recordCount, string sqlWhere, Dictionary<string, object> dbParams, params string[] selectFields) where TModel : ModelBase, new()
        {
            string sqlText;
            recordCount = GetRecordCount<TModel>(sqlWhere, dbParams);
            pageCount = (int)(Math.Ceiling(recordCount * 1.0 / onePageCount));
            GeneratePageSQLTextAndParam<TModel>(onePageCount, pageNo, isDesc, orderField, recordCount, sqlWhere, dbParams, out sqlText, selectFields);
            return GetDataTableBySQL(sqlText, dbParams);
        }
        public virtual DataTable GetDataTableTopCount<TModel>(int topCount, string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, params string[] selectFields) where TModel : ModelBase, new()
        {
            string sqlText;
            GenerateTopCountSQLText<TModel>(topCount, sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
            return GetDataTableBySQL(sqlText, dbParams);
        }

        #endregion

        #region 获得DataReader的几个方法

        public virtual DbDataReader GetDataReaderBySQL(string sqlText, Dictionary<string, object> dbParams)
        {
            DbParameter[] param = GetDbParam(dbParams);
            DbDataReader reader = DBHelper.GetDataReader(sqlText, param);
            return reader;
        }
        public virtual DbDataReader GetDataReader<TModel>(string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, params string[] selectFields) where TModel : ModelBase, new()
        {
            string sqlText;
            GenerateSearchSQLTextAndParam<TModel>(sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
            return GetDataReaderBySQL(sqlText, dbParams);
        }
        public virtual DbDataReader GetDataReaderByPage<TModel>(int onePageCount, int pageNo, bool isDesc, string[] orderFields, out int pageCount, out int recordCount, string sqlWhere, Dictionary<string, object> dbParams, params string[] selectFields) where TModel : ModelBase, new()
        {
            string sqlText;
            recordCount = GetRecordCount<TModel>(sqlWhere, dbParams);
            pageCount = (int)(Math.Ceiling(recordCount * 1.0 / onePageCount));
            GeneratePageSQLTextAndParam<TModel>(onePageCount, pageNo, isDesc, orderFields, recordCount, sqlWhere, dbParams, out sqlText, selectFields);
            return GetDataReaderBySQL(sqlText, dbParams);
        }
        public virtual DbDataReader GetDataReaderTopCount<TModel>(int topCount, string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, params string[] selectFields) where TModel : ModelBase, new()
        {
            string sqlText;
            GenerateTopCountSQLText<TModel>(topCount, sqlWhere, dbParams, orderFields, isDesc, out sqlText, selectFields);
            return GetDataReaderBySQL(sqlText, dbParams);
        }

        #endregion

        #region 获得List的几个方法

        /// <summary>
        /// 将DataReader转换为List对象
        /// </summary>
        protected virtual List<TModel> GetListByDataReader<TModel>(DbDataReader dbReader) where TModel : ModelBase, new()
        {
            if (dbReader == null) return null;
            List<TModel> list = new List<TModel>();
            using (dbReader)
            {
                if (dbReader.HasRows)
                {
                    DataTable schemaTable = dbReader.GetSchemaTable();
                    DataRow[] schemaRow;
                    while (dbReader.Read())
                    {
                        TModel model = new TModel();
                        Type type = model.GetType();
                        System.Reflection.PropertyInfo[] pros = type.GetProperties();
                        if (pros == null || pros.Length == 0) break;
                        foreach (var item in pros)
                        {
                            if (!item.CanWrite) continue;
                            string sName = item.Name;
                            //判断实体中的属性是否存在该列名,不存在则跳出循环
                            schemaRow = schemaTable.Select(string.Format("ColumnName='{0}'", sName));
                            if (schemaRow.Length == 0) continue;
                            if (dbReader[sName] != null && dbReader[sName].ToString().Trim() != string.Empty)
                            {
                                //如果DataReader没有将数据分类型存储,则使用以下方法转换
                                object obj;
                                if (dbReader[sName].GetType() != item.PropertyType)
                                {
                                    if (item.PropertyType is IConvertible)
                                    {
                                        obj = Convert.ChangeType(dbReader[sName], item.PropertyType);
                                    }
                                    else if (item.PropertyType == typeof(string))
                                    {
                                        obj = dbReader[sName].ToString();
                                    }
                                    else if (item.PropertyType == typeof(Guid))
                                    {
                                        obj = (Guid)dbReader[sName];
                                    }
                                    else
                                    {
                                        obj = dbReader[sName];
                                    }
                                }
                                else
                                {
                                    obj = dbReader[sName];
                                }
                                //object obj = dbReader[sName];
                                item.SetValue(model, obj, null);
                            }
                        }
                        list.Add(model);
                    }
                }
                else
                {
                    return null;
                }
                return list;
            }
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{               
            //    dbReader.Close();
            //    dbReader.Dispose();
            //}
        }

        public virtual List<TModel> GetList<TModel>(string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, params string[] selectFields) where TModel : ModelBase, new()
        {
            DbDataReader dbReader = GetDataReader<TModel>(sqlWhere, dbParams, orderFields, isDesc, selectFields);
            List<TModel> list = GetListByDataReader<TModel>(dbReader);
            return list;
        }

        public virtual List<TModel> GetListByPage<TModel>(int onePageCount, int pageNo, bool isDesc, string[] orderField, out int pageCount, out int recordCount, string sqlWhere, Dictionary<string, object> dbParams, params string[] selectFields) where TModel : ModelBase, new()
        {
            DbDataReader dbReader = GetDataReaderByPage<TModel>(onePageCount, pageNo, isDesc, orderField, out pageCount, out recordCount, sqlWhere, dbParams, selectFields);
            List<TModel> list = GetListByDataReader<TModel>(dbReader);
            return list;
        }

        public virtual List<TModel> GetListTopCount<TModel>(int topCount, string sqlWhere, Dictionary<string, object> dbParams, string[] orderFields, bool isDesc, params string[] selectFields) where TModel : ModelBase, new()
        {
            DbDataReader dbReader = GetDataReaderTopCount<TModel>(topCount, sqlWhere, dbParams, orderFields, isDesc, selectFields);
            List<TModel> list = GetListByDataReader<TModel>(dbReader);
            return list;
        }

        #endregion

        #region 执行存储过程

        public virtual int ExecProcedureGetNon(ProcedureObjBase procObj)
        {
            string procName = procObj.ToString();
            Dictionary<string, object> procParam = procObj.GetProcParamObj();
            DbParameter[] param = GetDbParam(procParam);
            procObj.SetOutParam(ref param);
            int r = DBHelper.ExecNonQuery(procName, CommandType.StoredProcedure, param);
            procObj.SetOutParamValue(param);
            return r;
        }
        public virtual DataTable ExecProcedureGetDataTable(ProcedureObjBase procObj)
        {
            string procName = procObj.ToString();
            Dictionary<string, object> procParam = procObj.GetProcParamObj();
            DbParameter[] param = GetDbParam(procParam);
            procObj.SetOutParam(ref param);
            DataTable r = DBHelper.GetDataTable(procName, CommandType.StoredProcedure, param);
            procObj.SetOutParamValue(param);
            return r;
        }
        public virtual object ExecProcedureGetObj(ProcedureObjBase procObj)
        {
            string procName = procObj.ToString();
            Dictionary<string, object> procParam = procObj.GetProcParamObj();
            DbParameter[] param = GetDbParam(procParam);
            procObj.SetOutParam(ref param);
            object r = DBHelper.GetScalar(procName, CommandType.StoredProcedure, param);
            procObj.SetOutParamValue(param);
            return r;
        }

        #endregion
    }
}
